if exists (select 1 from sys.procedures where name='startStandardTrace')
BEGIN
	drop procedure trace.startStandardTrace
END
go
/*****
Created by	:sfibich
ScriptName	:trace.proc.startStandardTrace.sql
Version		:1.6.120127
Purpose		:This creates a procedure that will start my "standard" trace.
Example		:exec trace.startStandardTrace 
Notes		:@traceSet - 0 basic trace
			 @traceSet - 1 Starting Events
			 @traceSet - 2 Cache events
*****/
create procedure trace.startStandardTrace
@traceLocation nVarchar(100) = N'C:\temp\'
,@maxfilesize bigInt= 10240
,@stopTime datetime = NULL
,@fileCount int =10
,@traceOptions int = 0
,@filter bit =1 
,@traceSet tinyInt=0
as
BEGIN
SET NOCOUNT ON
/*Variables*/
declare @isNumeric bit
declare @indexOf smallInt
declare @theString nvarchar(200)

/*Default Variables*/
declare @traceFile nvarchar(400)
declare @dateString nchar(8)
declare @userName sysname

/*set default variables*/
set @userName=substring(suser_name(),charindex('\',suser_name())+1,len(suser_name())-charindex('\',suser_name()))
set @dateString=convert(char(8),getdate(),112)
set @traceFile=@userName+@dateString
if @stopTime is null
	set @stopTime=dateadd(dd,2,getdate())


/*get existing files in the trace directory*/
declare @dirTree as table(
subdirectory varchar(255)
,depth int
,files int
)
insert into @dirTree
exec xp_dirTree @traceLocation,1,1

/*get existing file name attributes if the file exists*/
select
@theString=max(cast(replace(replace(subdirectory,@traceFile,''),'.trc','')as int))
,@isNumeric=isNumeric(left(replace(max(subdirectory),@traceFile,''),1))  
,@indexOf=charIndex('_',replace(max(subdirectory),@traceFile,'')) 
from
@dirTree
where
subDirectory like @traceFile+'%.trc'
and
depth=1

/*set the correct indexOf length if no '_' exists*/
if @indexOf=0
	set @indexOf=len(@theString)+1

IF (@isNumeric=0)
BEGIN
	select @traceFile=@traceLocation+@userName+@dateString+'1'
END
ELSE
BEGIN
	select @traceFile=@traceLocation+@userName+@dateString+cast(cast(substring(@theString,1,@indexOf-1) as int)+1 as varchar(10))
END

select @traceFile traceFileName

/*the trace*/
declare @rc int
declare @TraceID int

if @traceOptions=0
BEGIN
	exec @rc = sp_trace_create 
	@traceid=@TraceID output,
	@options=@traceOptions,
	@tracefile=@traceFile,
	@maxfilesize=@maxfilesize,
	@stoptime=@stopTime
END

if @traceOptions=2
BEGIN
	exec @rc = sp_trace_create 
	@traceid=@TraceID output,
	@options=@traceOptions,
	@tracefile=@traceFile,
	@maxfilesize=@maxfilesize,
	@stoptime=@stopTime,
	@filecount=@filecount
END
if (@rc != 0) goto error

/* Set the events */
declare @on bit
set @on = 1
/*Data File Auto Grow */
exec sp_trace_setevent @TraceID, 92, 8, @on  /*Host Name*/
exec sp_trace_setevent @TraceID, 92, 10, @on /*Application Name*/
exec sp_trace_setevent @TraceID, 92, 11, @on /*Login Name*/
exec sp_trace_setevent @TraceID, 92, 12, @on /*SPID*/
exec sp_trace_setevent @TraceID, 92, 13, @on /*Duration*/
exec sp_trace_setevent @TraceID, 92, 14, @on /*Start Time*/
exec sp_trace_setevent @TraceID, 92, 15, @on /*End Time*/
exec sp_trace_setevent @TraceID, 92, 35, @on /*Database Name*/
exec sp_trace_setevent @TraceID, 92, 4, @on  /*TransactionID*/
exec sp_trace_setevent @TraceID, 92, 51, @on /*EventSequence*/
/*
exec sp_trace_setevent @TraceID, 10, 1, @on  /*Text Data*/
exec sp_trace_setevent @TraceID, 21, 6, @on  /*NTUserName*/
exec sp_trace_setevent @TraceID,   , 9, @on  /*ClientProcessID*/
exec sp_trace_setevent @TraceID,   , 16, @on /*Reads*/
exec sp_trace_setevent @TraceID,   , 17, @on /*Writes*/
exec sp_trace_setevent @TraceID,   , 18, @on /*CPU*/
exec sp_trace_setevent @TraceID, 22, 31, @on /*Duration*/
exec sp_trace_setevent @TraceID, 92, 34, @on /*Object Name*/
exec sp_trace_setevent @TraceID, 92, 50, @on /*XactSequence*/
exec sp_trace_setevent @TraceID,   , 48, @on /*RowCounts*/
exec sp_trace_setevent @TraceID, 92, 63, @on /*SQL Handle*/
*/

/*Log File Auto Grow */
exec sp_trace_setevent @TraceID, 93, 15, @on
exec sp_trace_setevent @TraceID, 93, 8, @on
exec sp_trace_setevent @TraceID, 93, 12, @on
exec sp_trace_setevent @TraceID, 93, 13, @on
exec sp_trace_setevent @TraceID, 93, 10, @on
exec sp_trace_setevent @TraceID, 93, 14, @on
exec sp_trace_setevent @TraceID, 93, 11, @on
exec sp_trace_setevent @TraceID, 93, 35, @on
exec sp_trace_setevent @TraceID, 93, 51, @on

/*RPC:Completed*/
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 4, @on  
exec sp_trace_setevent @TraceID, 10, 50, @on 
exec sp_trace_setevent @TraceID, 10, 51, @on

if @traceSet & 1=1
BEGIN
/*RPC:Starting*/
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 4, @on  
exec sp_trace_setevent @TraceID, 11, 50, @on 
exec sp_trace_setevent @TraceID, 11, 51, @on

/*SP:Starting*/
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 6, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 4, @on  
exec sp_trace_setevent @TraceID, 42, 50, @on 
exec sp_trace_setevent @TraceID, 42, 51, @on

/*SQL:BatchStarting*/
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 4, @on  
exec sp_trace_setevent @TraceID, 13, 50, @on 
exec sp_trace_setevent @TraceID, 13, 51, @on

END

if @traceSet & 2=2
BEGIN
/*SP:CacheMiss*/
exec sp_trace_setevent @TraceID, 34, 8, @on
exec sp_trace_setevent @TraceID, 34, 1, @on
exec sp_trace_setevent @TraceID, 34, 6, @on
exec sp_trace_setevent @TraceID, 34, 10, @on
exec sp_trace_setevent @TraceID, 34, 14, @on
exec sp_trace_setevent @TraceID, 34, 34, @on
exec sp_trace_setevent @TraceID, 34, 11, @on
exec sp_trace_setevent @TraceID, 34, 12, @on
exec sp_trace_setevent @TraceID, 34, 4, @on  
exec sp_trace_setevent @TraceID, 34, 50, @on 
exec sp_trace_setevent @TraceID, 34, 51, @on

/*SP:CacheInsert*/
exec sp_trace_setevent @TraceID, 35, 8, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 6, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 14, @on
exec sp_trace_setevent @TraceID, 35, 11, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
exec sp_trace_setevent @TraceID, 35, 4, @on  
exec sp_trace_setevent @TraceID, 35, 50, @on 
exec sp_trace_setevent @TraceID, 35, 51, @on

/*SP:CacheRemove*/
exec sp_trace_setevent @TraceID, 36, 8, @on
exec sp_trace_setevent @TraceID, 36, 1, @on
exec sp_trace_setevent @TraceID, 36, 6, @on
exec sp_trace_setevent @TraceID, 36, 10, @on
exec sp_trace_setevent @TraceID, 36, 14, @on
exec sp_trace_setevent @TraceID, 36, 11, @on
exec sp_trace_setevent @TraceID, 36, 35, @on
exec sp_trace_setevent @TraceID, 36, 12, @on
exec sp_trace_setevent @TraceID, 36, 4, @on  
exec sp_trace_setevent @TraceID, 36, 50, @on 
exec sp_trace_setevent @TraceID, 36, 51, @on

/*SP:Recompile*/
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 34, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 35, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 63, @on 
exec sp_trace_setevent @TraceID, 37, 4, @on  
exec sp_trace_setevent @TraceID, 37, 50, @on 
exec sp_trace_setevent @TraceID, 37, 51, @on

/*SP:CacheHit*/
exec sp_trace_setevent @TraceID, 38, 8, @on
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 6, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 14, @on
exec sp_trace_setevent @TraceID, 38, 34, @on
exec sp_trace_setevent @TraceID, 38, 11, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 38, 4, @on  
exec sp_trace_setevent @TraceID, 38, 50, @on 
exec sp_trace_setevent @TraceID, 38, 51, @on
END

/*SP:Completed*/
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 4, @on  
exec sp_trace_setevent @TraceID, 43, 50, @on 
exec sp_trace_setevent @TraceID, 43, 51, @on

/*SP:StmtCompleted*/
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 4, @on  
exec sp_trace_setevent @TraceID, 45, 50, @on 
exec sp_trace_setevent @TraceID, 45, 51, @on

/*SQL:BatchCompleted*/
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 4, @on  
exec sp_trace_setevent @TraceID, 12, 50, @on 
exec sp_trace_setevent @TraceID, 12, 51, @on

/*SQL:StmtCompleted*/
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 4, @on  
exec sp_trace_setevent @TraceID, 41, 50, @on 
exec sp_trace_setevent @TraceID, 41, 51, @on

/*Login*/
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 51, @on

/*LogOut*/
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 51, @on


/* Set the Filters */
declare @intfilter int
declare @bigintfilter bigint

/*filter out durations not greater than a milisecond*/
if @filter=1
BEGIN
	set @bigintfilter = 1000
	exec sp_trace_setfilter @TraceID, 13, 0, 2, @bigintfilter
END

/* Set the trace status to start */
exec sp_trace_setstatus @TraceID, 1

/* display trace id for future references */
select @TraceID TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 

END
go
if exists (select 1 from sys.objects where name='startStandardTrace' and SCHEMA_NAME(schema_id)='trace')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'trace.startStandardTrace' as info_desc
	,'1.6.120127' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END
